﻿
-- =============================================
-- Author:		<Author,XFL>
-- Create date: <Create Date,2015 02 10>
-- Description:	<Description, 
-- 
-- [财务管理]-[工资发放]-[发放工资]
-- 部门查询是 使用了部门编号查询 BMBH >
-- =============================================
CREATE PROCEDURE [dbo].[proc_Wage_Getlist_FF]
	(
		@CompanyId int,
		@DepId int,
		@Month nvarchar(7),
		@EmpState int,
		@HRFlag int,
		@FinanceFlag int,
		@FinanceFlag_DF int
	)
AS
Begin
	declare @Sql NVARCHAR(4000),@sqlStr nvarchar(500)
	set @Sql=''
	set @sqlStr=''
	if @CompanyId<>0
	begin
		set @Sql=@Sql+' Companyid='+convert(varchar,@CompanyId)+' and ' 
		set @sqlStr=@sqlStr+' Companyid='+convert(varchar,@CompanyId)+' and ' 
	end
	
	if @HRFlag<>0 
	begin	
		set @Sql=@Sql+' HRFlag='+convert(varchar,@HRFlag)+' and ' 
	end
	if @FinanceFlag<>0
	begin
		set @Sql=@Sql+' FinanceFlag='+convert(varchar,@FinanceFlag)+' and ' 
	end
	if @FinanceFlag_DF<>0
	begin
		set @Sql=@Sql+' FinanceFlag_DF='+convert(varchar,@FinanceFlag_DF)+' and ' 
	end
	if @EmpState<>0
	begin
		set @Sql=@Sql+' StateId='+convert(varchar,@EmpState)+' and ' 
	end
	if @DepId<>0
	begin
		set @Sql=@Sql+' (DepartmentId in(select c.nid from @indextable c)) and '
		set @sqlStr=@sqlStr+' (DepartmentId in(select c.nid from @indextable c)) and '
	end
	
set @Sql='	
	
			declare @bmbh_T  varchar(50)
			set @bmbh_T = (select bmbh  FROM department WHERE id='+convert(varchar,@DepId)+')		
			declare @indextable table(id int identity(1,1),nid int)
			insert into @indextable(nid) Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T;

		-- ====================================================
		--	添加 查询总共工资数据记录数[含在职数、离职数] 
		--	     查询通过核算工资记录数[含在职数、离职数] 
		--	XFL 20111230
		-- ====================================================
		-- 查询总共工资数据记录数
		declare @Record_count int
		set @Record_count= (select count(0) From Employee_Wage Where '+@sqlStr+' Convert(nchar(7),FaFangMonth,120)='''+@Month+''' )
		
		-- 在职数
		declare @Record_count_State_1 int
		set @Record_count_State_1 =(select count(0) From Employee_Wage Where '+@sqlStr+' StateId=1 and Convert(nchar(7),FaFangMonth,120)='''+@Month+''')

		-- 离职数 >> Record_count- Record_count_State_1

		-- 查询通过核算工资记录数
		declare @Record_count_HRFlag_2 int
		set @Record_count_HRFlag_2=(select count(0) From Employee_Wage Where '+@sqlStr+' HRFlag=2 and Convert(nchar(7),FaFangMonth,120)='''+@Month+''')

		-- 在职数
		declare @Record_count_HRFlag_2_State_1 int
		set @Record_count_HRFlag_2_State_1=(select count(0) From Employee_Wage Where '+@sqlStr+' HRFlag=2 and StateId=1 and Convert(nchar(7),FaFangMonth,120)='''+@Month+''' )

		-- 离职数 >> Record_count_HRFlag_2 - Record_count_HRFlag_2_State_1

	;WITH list As(Select --ROW_NUMBER() OVER (ORDER BY DepartmentId,username) AS Row,
		Id
		,GId
		,UserName
		,EName
		,CompanyId
		,(Select CompanyName From Company Where Id=Employee_Wage.CompanyId) As CompanyName
		,DepartmentId
		,DepName
		,ZhiWu
		,GradeId
		,GradeName
		,IsNull(PostCategoryId,0) as PostCategoryId
		,PostCategoryName
		,PostId
		,PostName
		,ChuQinDays
		,BasicWage
		,PostWage
		,WorkWage
		,JxWage
		,ManQin
		,Allowance		
		,QtAllowance
		,TiCheng
		,MTiCheng
		,YjBonus
		,LsBonus
		,ChuQinKouKuan
		,BxKouKuan
		,ChiDaoKouKuan
		,YJKouKuan
		,OtherKouKuan
		,HeJiWage
		,FaFangMonth
		,CreateName
		,CreateDate
		,HRFlag
		,ISNULL(HRFlagDate,''1900-01-01'') As HRFlagDate
		,FinanceFlag
		,ISNULL(FinanceFlagDate,''1900-01-01'') As FinanceFlagDate
		,Guid_str
		,isnull(XSTC,0) XSTC, isnull(XFTC,0) XFTC, isnull(BLTC,0) BLTC, isnull(FAllowance,0) FAllowance,
		 isnull(LSwance,0) LSwance, isnull(ZBwance,0) ZBwance, 
		 isnull(GJJKouKuan,0) GJJKouKuan, 
		 isnull(GRSDSKouKuan,0) GRSDSKouKuan, isnull(YFHeJi,0) YFHeJi, isnull(DFHeJi,0) DFHeJi, 
		 FinanceFlagDate_DF, isnull(FinanceFlag_DF,1) FinanceFlag_DF
		 ,StateId,WageCardNo,isnull(Deduction,0) as Deduction,
		 isnull(bxje,0) as bxje,
		    isnull(msje,0) as msje
		From Employee_Wage Where '+@Sql+' Convert(nchar(7),FaFangMonth,120)='''+@Month +'''
		
	)
	Select ROW_NUMBER() OVER (ORDER BY (select bmbh from Department where Id=list.DepartmentId),username) row,*
	,@Record_count as Record_count
	,@Record_count_State_1 as Record_count_State_1
	,@Record_count_HRFlag_2 as Record_count_HRFlag_2
	,@Record_count_HRFlag_2_State_1 as Record_count_HRFlag_2_State_1
	,convert(varchar(10),isnull((select ruzhidate from Employee where list.UserName=employee.username ) ,''''),120) as ruzhidate
,(select sum(YFHeJi) from list ) as YFHeJi_zj,
(select sum(bxje) from list ) as bxje_zj
From list --order by UserName
'
print (@Sql)
exec (@Sql)
End
	RETURN
